<?php
include_once dirname(__FILE__).'/../RDBConfig.php';
include_once dirname(__FILE__).'/../../interface/UserDAOInterface.php';
include_once dirname(__FILE__).'/../../object/User.php';

class UserDAO implements UserDAOInterface {

	public static $TABLE_NAME = 'USER';
	public static $COL_PK = 'USER_PK';
	public static $COL_USER_ID = 'USER_ID';
	public static $COL_USER_NAME = 'USER_NAME';
	public static $COL_PASSWORD = 'PASSWORD';
	public static $COL_EMAIL = 'EMAIL';
	
	public function __construct() {
		
	}
	
	/**
	 * Insert new user
	 * @param User $user
	 * @return int
	 */
	public function insert($user) {
		$tableName = self::$TABLE_NAME;
		$colPK = self::$COL_PK;
		$colUserID = self::$COL_USER_ID;
		$colUserName = self::$COL_USER_NAME;
		$colPassword = self::$COL_PASSWORD;
		$colEmail = self::$COL_EMAIL;
	
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
	
		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
		$statement = <<<END
        INSERT INTO $tableName
        	($colPK, $colUserID, $colUserName, $colPassword, $colEmail)
                VALUES (:pk, :userid, :username, :pass, :email)
END;
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$data = array( // chuan bi du lieu
				':pk' => $user->getUserPK(),
				':userid' => $user->getUserID(),
				':username' => $user->getUserName(),
				':pass' => md5($user->getPassword()), // ma hoa mat khau bang md5
				':email' => $user->getEmail()
		);
	
		try {
			$conn->beginTransaction(); // bo che do tu dong commit
				
			$sql->execute($data); // do du lieu vao cau truy van
				
			$conn->commit(); // commit de cap nhat thay doi vao CSDL
				
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
			$conn->rollBack();
		}
	
		$conn = null; // free memory
		return $sql->rowCount(); // tra ve so dong da them
	}
	
	/**
	 * Delete user
	 * @param User $user
	 * @return int
	 */
	public function delete($user) {
		$tableName = self::$TABLE_NAME;
		$colPK = self::$COL_PK;
	
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
	
		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
		$statement = <<<END
        DELETE FROM $tableName
        	 WHERE $colPK = :pk
END;
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$data = array( // chuan bi du lieu
				':pk' => $user->getUserPK()
		);
	
		try {
			$conn->beginTransaction(); // bo che do tu dong commit
	
			$sql->execute($data); // do du lieu vao cau truy van
	
			$conn->commit(); // commit de cap nhat thay doi vao CSDL
	
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
			$conn->rollBack();
		}
	
		$conn = null; // free memory
		return $sql->rowCount(); // tra ve so dong da them
	}
	
	/**
	 * Update user
	 * @param User $old
	 * @param User $new
	 * @return number of rows were updated
	 */
	public function update($old, $new) {
		$tableName = self::$TABLE_NAME;
		$colPK = self::$COL_PK;
		$colUserID = self::$COL_USER_ID;
		$colUserName = self::$COL_USER_NAME;
		$colPassword = self::$COL_PASSWORD;
		$colEmail = self::$COL_EMAIL;
	
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
	
		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
		$statement = <<<END
        UPDATE $tableName
        	SET 
        		$colUserName = :username,
        		$colPassword = :pass,
        		$colEmail = :email
           WHERE $colPK = :pk
END;
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$data = array( // chuan bi du lieu
				':pk' => $old->getUserPK(),
				':username' => $new->getUserName(),
				':pass' => md5($new->getPassword()), // ma hoa mat khau bang md5
				':email' => $new->getEmail()
		);
	
		try {
			$conn->beginTransaction(); // bo che do tu dong commit
	
			$sql->execute($data); // do du lieu vao cau truy van
	
			$conn->commit(); // commit de cap nhat thay doi vao CSDL
	
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
			$conn->rollBack();
		}
	
		$conn = null; // free memory
		return $sql->rowCount(); // tra ve so dong da them
	}
	
	/**
	 * Authentication user login
	 * Return User(0, '', '', '', '') if FAIL
	 * @param int $id
	 * @return User $user
	 */
	public function authenticate($userID, $pass) {
		$tableName = self::$TABLE_NAME;
		$colPK = self::$COL_PK;
		$colUserID = self::$COL_USER_ID;
		$colUserName = self::$COL_USER_NAME;
		$colPassword = self::$COL_PASSWORD;
		$colEmail = self::$COL_EMAIL;
	
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
	
		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
		$statement = <<<END
        SELECT *
        FROM $tableName
        WHERE 	$colUserID = :userid
        AND $colPassword = :pass
END;
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$data = array( // chuan bi du lieu
				':userid' => $userID,
				':pass' => md5($pass)
		);
	
		try {
			$sql->execute($data); // do du lieu vao cau truy van
			$sql->setFetchMode(PDO::FETCH_ASSOC);
			if($sql->rowCount() > 0) {
				$row = $sql->fetch();
				$user = new User($row["$colPK"], $row["$colUserID"],
						$row["$colUserName"], $row["$colPassword"],
						$row["$colEmail"]);
				return $user;
			}
				
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
		}
		$conn = null; // free memory
	
		return new User(0, null, null, null, null);
	
	}
	
	/**
	 * Tra ve gia tri User PK lon nhat
	 * @see UserDAOInterface::getMaxPK()
	 * @return int
	 */
	public function getMaxPK() {
		$table = self::$TABLE_NAME;
		$colUserPK = self::$COL_PK;
	
		$statement = <<<END
        SELECT MAX($colUserPK)
        FROM $table
END;
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
	
		$sql = $conn->prepare($statement); // chuan bi cau truy van
	
		try {
			$sql->execute(); // do du lieu vao cau truy van
			$sql->setFetchMode(PDO::FETCH_ASSOC);
			if($sql->rowCount() > 0) {
				$row = $sql->fetch();
				$max = $row["MAX($colUserPK)"];
				return $max;
			}
	
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
		}
		$conn = null; // free memory
	
		return 0;
	}
	
	public function getUserByPK($pk) {
		
	}
	
	
	
	// ============ TESTED =============
	
	
	
	
	
// 	/**
// 	 * Select user
// 	 * @param User $user
// 	 * @return array user
// 	 */
// 	public function selectUser($user) {
// 		$tableName = self::$TABLE_NAME;
// 		$colPK = self::$COL_PK;
// 		$colUserID = self::$COL_USER_ID;
// 		$colUserName = self::$COL_USER_NAME;
// 		$colPassword = self::$COL_PASSWORD;
// 		$colEmail = self::$COL_EMAIL;
	
// 		// tao ket noi
// 		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
// 				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
// 		$conn = RDBDAOFactory::createConnection($rdbConfig);
	
// 		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
// 		$statement = <<<END
//         SELECT $colPK, $colUserID, $colUserName, $colPassword, $colEmail
//         FROM $tableName
//         WHERE 	$colUserID = :id
//         	OR	$colUserName = :username
//         	OR	$colEmail = :email
// END;
// 		$sql = $conn->prepare($statement); // chuan bi cau truy van
// 		$data = array( // chuan bi du lieu
// 				':id' => $user->getUserID(),
// 				':username' => $user->getUserName(),
// 				':email' => $user->getEmail()
// 		);
		
// 		$users = array();
// 		try {
// 			$sql->execute($data); // do du lieu vao cau truy van
// 			$sql->setFetchMode(PDO::FETCH_ASSOC);
// 			foreach ($sql->fetchAll() as $user) {
// 				$users[] = new User($user["$colPK"], $user["$colUserID"],
// 						$user["$colUserName"], $user["$colPassword"],
// 						$user["$colEmail"]);
// 			}
// 		} catch (PDOException $excs) { // neu co loi thi cuon lai
// 			echo $excs->getMessage().'<br>';
// 		}
// 		$conn = null; // free memory
		
// 		return $users;
// 	}
	
// 	/**
// 	 * Kiem tra su ton tai
// 	 * @param User $user
// 	 * @return User
// 	 */
// 	public function checkUser($user) {
// 		$tableName = self::$TABLE_NAME;
// 		$colPK = self::$COL_PK;
// 		$colUserID = self::$COL_USER_ID;
// 		$colUserName = self::$COL_USER_NAME;
// 		$colPassword = self::$COL_PASSWORD;
// 		$colEmail = self::$COL_EMAIL;
		
// 		// tao ket noi
// 		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
// 				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
// 		$conn = RDBDAOFactory::createConnection($rdbConfig);
		
// 		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
// 		$statement = <<<END
//         SELECT $colPK, $colUserID, $colUserName, $colPassword, $colEmail
//         FROM $tableName
//         WHERE 	$colUserID = :id
//         	AND	$colPassword = :password
// END;
// 		$sql = $conn->prepare($statement); // chuan bi cau truy van
// 		$data = array( // chuan bi du lieu
// 				':id' => $user->getUserID(),
// 				':password' => $user->getPassword()
// 		);
		
// 		try {
// 			$sql->execute($data); // do du lieu vao cau truy van
// 			$sql->setFetchMode(PDO::FETCH_ASSOC);
// 			if($sql->rowCount() > 0) {
// 				$row = $sql->fetch();
// 				$user = new User($row["$colPK"], $row["$colUserID"],
// 					$row["$colUserName"], $row["$colPassword"],
// 					$row["$colEmail"]);
// 				return $user;
// 			}
			
// 		} catch (PDOException $excs) { // neu co loi thi cuon lai
// 			echo $excs->getMessage().'<br>';
// 		}
// 		$conn = null; // free memory
		
// 		return new User(null, null, null, null, null);		
// 	}
}
?>